﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
ALTER TABLE dbo.bht_Groups ADD
	[ReadOnly] bit NOT NULL CONSTRAINT DF_bht_Groups_ReadOnly DEFAULT 0
GO
ALTER TABLE dbo.bht_Groups SET (LOCK_ESCALATION = TABLE)
GO
IF TYPE_ID('Ids') IS NULL
	CREATE TYPE [dbo].[Ids] AS TABLE(
		[Id] [int] NOT NULL
	)
GO
CREATE PROCEDURE [dbo].[bhp_NemoGroupGetByIds] (
	@Ids				Ids READONLY
) 
WITH ENCRYPTION
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 'Table' WHEN 'V' THEN 'View' WHEN 'TE' THEN 'ExtendedTable' ELSE '' END as [Type],
		ISNULL(g.EnableTranslate, 1) AS EnableTranslate,
		t.parent as ParentId,
		ISNULL(g.[ReadOnly], 0) as 'ReadOnly'
	FROM 
		@Ids x
		INNER JOIN n_Tree t ON x.Id = t.id
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('V', 'T', 'TE')
		AND (parent = 0 OR typ = 'TE')

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupGet] (
	@Id				INT
)
WITH ENCRYPTION
AS
BEGIN

	DECLARE
		@Ids Ids

	INSERT INTO @Ids VALUES (@Id)

	EXEC bhp_NemoGroupGetByIds @Ids
	
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupGetAll]
WITH ENCRYPTION
AS
BEGIN

	DECLARE
		@Ids Ids

	INSERT INTO @Ids 
		SELECT
			id
		FROM
			n_Tree
		WHERE
			(del IS NULL OR del = 0)
			AND typ IN ('V', 'T', 'TE')
			AND (parent = 0 OR typ = 'TE')

	EXEC bhp_NemoGroupGetByIds @Ids

END
